package com.apestech.api.query;

import com.apestech.framework.db.FieldFormat;
import com.apestech.framework.db.Format;
import com.apestech.framework.db.SqlResultSet;
import com.apestech.framework.json.JSON;
import com.apestech.framework.json.JSONArray;
import com.apestech.framework.json.JSONObject;
import com.apestech.framework.sql.condition.ConditionMap;
import com.apestech.framework.util.DateUtil;
import com.apestech.framework.util.FileUtil;
import com.apestech.framework.util.FormatUtil;
import com.apestech.framework.util.StringUtil;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFDataFormat;

import java.io.FileOutputStream;
import java.io.OutputStream;
import java.math.BigDecimal;
import java.sql.ResultSetMetaData;
import java.util.Calendar;

/**
 * 写XLS
 *
 * @author hup
 * @create 2018-05-30-17:12
 */
public class XLSWrite extends StringUtil {

    private SqlResultSet rs;
    private Workbook wb=null;
    private JSONArray head;
    private String xlsName=null;
    private String sheetName=null;
    private int colsize=-1;
    private long maxSheetRow=20000;   //xlxs默认最大值为1048575，所在数据范围为0...1048575之间
    private long maxXlsRow=-1;   //xlxs文件默认最大行数
    private int count;
    private CellStyle headstyle;
    private boolean isMaxFile=false;
    private int order=1;
    private Format format;
    private FieldFormat fieldFormat;
    private CellStyle date_style;
    private CellStyle num_style;
    private XSSFDataFormat cellformat;

    public XLSWrite() {}

    public XLSWrite(SqlResultSet rs) throws Exception {
        iniRs(rs);
    }

    public void iniRs(SqlResultSet rs) throws Exception {
        this.rs=rs;
        this.fieldFormat = new FieldFormat();
        if(rs.getConParam()!=null){
            this.format=rs.getConParam().getFormat();
            this.fieldFormat.setFormat(rs.getConParam().getFormat(), rs.getConParam().isXls(), rs.getConParam().isBmath());
        }
        if(this.wb==null){
            this.wb = new SXSSFWorkbook(100);
            ((SXSSFWorkbook)this.wb).setCompressTempFiles(true);
            setHeadStyle();
            cellformat= (XSSFDataFormat) this.wb.createDataFormat();
            date_style=this.wb.createCellStyle();
            num_style=this.wb.createCellStyle();
        }
    }

    public String getXlsName() {
        return xlsName;
    }

    public long getMaxSheetRow() {
        return maxSheetRow;
    }

    public void setMaxSheetRow(long maxSheetRow) {
        this.maxSheetRow = maxSheetRow;
    }

    public long getMaxXlsRow() {
        return maxXlsRow;
    }

    public void setMaxXlsRow(long maxXlsRow) {
        this.maxXlsRow = maxXlsRow;
    }

    public int getCount() {
        return count;
    }

    public boolean isMaxFile() {
        return isMaxFile;
    }

    public void setOrder(int order) {
        this.order = order;
    }

    public void setHead(JSONArray head){
        this.head=head;
        this.colsize=this.head.size();
    }

    public void setXlsName(String xlsName){
        this.xlsName=xlsName;
    }

    public String getSheetName() {
        return sheetName;
    }

    public void setSheetName(String sheetName) {
        this.sheetName = sheetName;
    }

    public void writeXls() throws Exception {
        SXSSFSheet sh=null;
        this.count=0;
        int sheetcount=0;
        String shName=sheetName;
        if(shName==null) {
            shName=xlsName;
        }
        if(shName!=null){
            sh= (SXSSFSheet) this.wb.createSheet(shName+castToString(this.order));
        } else {
            sh= (SXSSFSheet) this.wb.createSheet();
        }
        sh.createFreezePane( 0, 1, 0, 1 );
        writeHead(sh);
        int result=0;
        result=writeSheet(sh, 1);
        while(result!=0){
            sheetcount++;
            sh= (SXSSFSheet) this.wb.createSheet(String.valueOf(sheetcount));
            sh.createFreezePane( 0, 1, 0, 1 );
            writeHead(sh);
            result=writeSheet(sh, 1);
        }
    }

    private int writeSheet(SXSSFSheet sh, int i) throws Exception {
        while(this.rs.next()){
            Row row = sh.createRow(i);
            writeRow(sh, row);
            i++;
            this.count++;
            //System.out.println(this.count);
            if(this.count==this.maxXlsRow){
                isMaxFile=true;
                break;
            }
            if(this.count % maxSheetRow == 0){
                return 1;
            }
        }
        return 0;
    }

    private void writeRow(SXSSFSheet sh, Row row) throws Exception {
        for(int i = 0; i < this.colsize; i++){
            JSONObject map= JSON.toJSON(this.head.get(i));
            String field=map.getString("field");
            Cell cell = row.createCell(i);
            Object value=rs.getObject(field);
            if(rs.getConParam()!=null && rs.getConParam().getFieldValueCallback()!=null && value!=null){
                value=rs.getConParam().getFieldValueCallback().getValue(field, value);
            }
            try{
                setCell(field, value, cell);
            } catch (Exception e){
                throw new Exception("列名无效["+field+":"+castToString(value)+"]"+e.toString());
            }

        }
    }

    private void writeHead(SXSSFSheet sh) throws Exception {
        Row row = sh.createRow(0);
        for(int i = 0; i < this.colsize; i++){
            JSONObject map= JSON.toJSON(this.head.get(i));
            String title=map.getString("title");
            Cell cell = row.createCell(i);
            setCell(null, title, cell);
            cell.setCellStyle(this.headstyle);
        }
    }

    public String saveToFile(String filename, boolean hasDir) throws Exception {
        String file;
        if(maxXlsRow>0 && hasDir){
            file=filename+this.xlsName+"/"+this.xlsName+this.order+".xlsx";
        } else {
            file=filename+this.xlsName+".xlsx";
        }
        FileUtil.newFile(file);
        FileOutputStream out = new FileOutputStream(file);
        this.wb.write(out);
        out.close();
        return file;
    }

    public String saveToFile(OutputStream out) throws Exception {
        String file="";
//        if(maxXlsRow>0 && hasDir){
//            file=filename+this.xlsName+"/"+this.xlsName+this.order+".xlsx";
//        } else {
//            file=filename+this.xlsName+".xlsx";
//        }
//        FileUtil.newFile(file);
//        FileOutputStream out = new FileOutputStream(file);
        this.wb.write(out);
        out.close();
        return file;
    }

    private void setCell(String field, Object value, Cell cell) throws Exception{
        if("AMOUNT".equals(field)){
            System.out.println("");
        }
        if(value==null){
            return;
        }
        String valueClass=value.getClass().getName();
        switch(valueClass){
            case "oracle.sql.TIMESTAMP":
                setDate(field, cell, value, valueClass);
                break;
            case "java.sql.Timestamp":
                setDate(field, cell, value, valueClass);
                break;
            case "java.math.BigDecimal":
                setNumber(field, cell, value);
                break;
            default:
                cell.setCellValue(String.valueOf(value));
                break;
        }
    }

    private void setNumber(String field, Cell cell, Object value) throws Exception {
        if(new BigDecimal(((BigDecimal)value).intValue()).compareTo((BigDecimal)value)==0){
            setLong(field, cell, value);
        }else{
            setDouble(field, cell, value);
        }
    }

    private void setLong(String field, Cell cell, Object value) throws Exception{
        if(rs.getConParam()!=null && rs.getConParam().getHtw()!=null){
            String sfield=rs.getConParam().getHtw().getGridHead().getPivotmap().getString(field);
            if(sfield!=null) field=sfield;
        }
        FieldFormat.XLSField xlsfield=null;
        if(fieldFormat!=null) xlsfield=fieldFormat.getXLSField(field, value);
        if(xlsfield!=null && xlsfield.getPattern()!=null){
            num_style.setDataFormat(cellformat.getFormat(xlsfield.getPattern()));
            cell.setCellStyle(num_style);
        }
        if(xlsfield!=null && xlsfield.getFormatValue()!=null) cell.setCellValue(this.castToLong(xlsfield.getFormatValue()));
        else cell.setCellValue(this.castToLong(value));
    }

    private void setDouble(String field, Cell cell, Object value) throws Exception{
        if(rs.getConParam()!=null && rs.getConParam().getHtw()!=null){
            String sfield=rs.getConParam().getHtw().getGridHead().getPivotmap().getString(field);
            if(sfield!=null) field=sfield;
        }
        FieldFormat.XLSField xlsfield=null;
        if(fieldFormat!=null) xlsfield=fieldFormat.getXLSField(field, value);
        if(xlsfield!=null && xlsfield.getPattern()!=null){
            num_style.setDataFormat(cellformat.getFormat(xlsfield.getPattern()));
            cell.setCellStyle(num_style);
        }
        if(xlsfield!=null && xlsfield.getFormatValue()!=null) cell.setCellValue(this.castToDouble(xlsfield.getFormatValue()));
        else cell.setCellValue(this.castToDouble(value));
    }

    private void setDate(String field, Cell cell, Object value, String valueClass) throws Exception {
        Calendar calendar = Calendar.getInstance();
        if("java.util.Date".equals(valueClass)){
            calendar.setTime((java.util.Date)value);
            cell.setCellValue(calendar);
        } else if("oracle.sql.TIMESTAMP".equals(valueClass)){
            calendar.setTime(new java.util.Date(((oracle.sql.TIMESTAMP)value).timestampValue().getTime()));
            setCellDate(field, cell, calendar);
        } else if("java.sql.Timestamp".equals(valueClass)){
            calendar.setTime(new java.util.Date(((java.sql.Timestamp)value).getTime()));
            setCellDate(field, cell, calendar);
        } else if("java.lang.String".equals(valueClass)){
            calendar.setTime(DateUtil.strToDate((String) value));
            cell.setCellValue(calendar);
        }
    }

    private void setHeadStyle(){
        this.headstyle=this.wb.createCellStyle();
        this.headstyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        this.headstyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        this.headstyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        this.headstyle.setFillPattern(CellStyle.SOLID_FOREGROUND);

        Font font=this.wb.createFont();
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        font.setFontHeightInPoints((short) 12);
        this.headstyle.setFont(font);
    }

    private void setCellDate(String field, Cell cell, Calendar calendar) throws Exception {
        String type="";
        if(format!=null && format.getFieldById(field)!=null){
            type=format.getFieldById(field).getType();
        }
        XSSFDataFormat cellformat= (XSSFDataFormat) this.wb.createDataFormat();
        if("timeStamp".equals(type)) date_style.setDataFormat(cellformat.getFormat("yyyymmdd.hhmmss.000"));
        else date_style.setDataFormat(cellformat.getFormat("yyyy-m-d hh:mm:ss"));
        cell.setCellValue(calendar);
        cell.setCellStyle(date_style);
    }
}
